#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto

${HIVE_HOME} -S -e "
-- 建库
create database if not exists zx_dwb;
use zx_dwb;

-- 建表
drop table if exists zx_dwb.dwb_signin_wide;
create table zx_dwb.dwb_signin_wide(
    --以打卡表为主表，主表中的字段
    id                int       comment '打卡信息表主键id',
    class_id          int       comment '班级id',
    student_id        int       comment '学员id',
    signin_time       string    comment '签到时间',
    signin_date       string    comment '签到日期',
    --关联其他表得到的字段
    --班级作息时间表
    morning_begin_time    string    comment '上午开始时间',
    morning_end_time      string    comment '上午结束时间',
    afternoon_begin_time  string    comment '下午开始时间',
    afternoon_end_time    string    comment '下午结束时间',
    evening_begin_time    string    comment '晚上开始时间',
    evening_end_time      string    comment '晚上结束时间',
    --班级排课信息表
	class_date          string      comment '上课日期',
    content             string      comment '课程内容'
)
comment '学生打卡信息宽表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists zx_dwb.dwb_leave_apply_wide;
create table zx_dwb.dwb_leave_apply_wide(
    --以请假表为主表，主表中的字段
    id                int       comment '请假信息表主键id',
    class_id          int       comment '班级id',
    student_id        int       comment '学员id',
    begin_time      string      comment '请假开始时间',
    end_time        string      comment '请假结束时间',
    --关联其他表得到的字段
    --班级作息时间表
    morning_begin_time    string    comment '上午开始时间',
    morning_end_time      string    comment '上午结束时间',
    afternoon_begin_time  string    comment '下午开始时间',
    afternoon_end_time    string    comment '下午结束时间',
    evening_begin_time    string    comment '晚上开始时间',
    evening_end_time      string    comment '晚上结束时间',
    --班级排课信息表
	class_date          string      comment '上课日期',
    content             string      comment '课程内容'
)
comment '学生请假申请信息宽表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');
"
wait

${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "
use hive.zx_dwb;

insert into hive.zx_dwb.dwb_signin_wide
select
       signin.id as id,
       signin.class_id as class_id,
       student_id,
       signin_time,
       signin_date,
       morning_begin_time,
       morning_end_time,
       afternoon_begin_time,
       afternoon_end_time,
       evening_begin_time,
       evening_end_time,
       class_date,
       content
from hive.zx_dwd.fact_tbh_student_signin_record signin
left join hive.zx_dwd.dim_tbh_class_time_table time_table on signin.time_table_id = time_table.id
join hive.zx_dwd.dim_course_table_upload_detail course on signin.class_id = course.class_id and signin.signin_date = course.class_date
where course.class_date between time_table.use_begin_date and time_table.use_end_date;

insert into hive.zx_dwb.dwb_leave_apply_wide
select
       leave.id as id,
       leave.class_id as class_id,
       student_id,
       begin_time,
       end_time,
       morning_begin_time,
       morning_end_time,
       afternoon_begin_time,
       afternoon_end_time,
       evening_begin_time,
       evening_end_time,
       class_date,
       content
from hive.zx_dwd.fact_student_leave_apply leave
left join hive.zx_dwd.dim_tbh_class_time_table time_table on leave.class_id = time_table.class_id
left join hive.zx_dwd.dim_course_table_upload_detail course on leave.class_id = course.class_id
where substring (end_time,1,10) >= time_table.use_begin_date and substring (begin_time,1,10) <= time_table.use_end_date;
"

